package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import dao.EmpDao;
import entity.Emp;
import util.DBUtils;

public class EmpDaoImpl implements EmpDao{

	@Override
	public boolean insert(Emp emp) {
		int row = 0;
		try {
			Connection c = DBUtils.getConnection();
			String sql = "insert into emp_copy values(?,?,?,?,?,?,?,?)";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setObject(1, emp.getEmpno());
			ps.setObject(2, emp.getEname());
			ps.setObject(3, emp.getJob());
			ps.setObject(4, emp.getMgr());
			ps.setObject(5, emp.getHiredate());
			ps.setObject(6, emp.getSal());
			ps.setObject(7, emp.getComm());
			ps.setObject(8, emp.getDeptno());
			row = ps.executeUpdate();
			c.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return row == 1;
	}

	@Override
	public boolean update(Emp emp) {
		int row = 0;
		try {
			Connection c = DBUtils.getConnection();
			String sql = "update emp_copy set ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? where empno=?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setObject(8, emp.getEmpno());
			ps.setObject(1, emp.getEname());
			ps.setObject(2, emp.getJob());
			ps.setObject(3, emp.getMgr());
			ps.setObject(4, emp.getHiredate());
			ps.setObject(5, emp.getSal());
			ps.setObject(6, emp.getComm());
			ps.setObject(7, emp.getDeptno());
			row = ps.executeUpdate();
			c.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return row == 1;
	}

	@Override
	public boolean deleteById(int empno) {
		int row = 0;
		try {
			Connection c = DBUtils.getConnection();
			String sql = "delete from emp_copy where empno=?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setObject(1, empno);
			row = ps.executeUpdate();
			c.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return row == 1;
	}

	@Override
	public Emp selectById(int empno) {
		Emp emp = null;
		try {
			Connection c = DBUtils.getConnection();
			String sql = "select * from emp_copy where empno=?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setObject(1, empno);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				emp = new Emp();
				emp.setEmpno(rs.getInt(1));
				emp.setEname(rs.getString(2));
				emp.setJob(rs.getString(3));
				emp.setMgr(rs.getInt(4));
				emp.setHiredate(rs.getDate(5));
				emp.setSal(rs.getInt(6));
				emp.setComm(rs.getInt(7));
				emp.setDeptno(rs.getInt(8));
			}
			c.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return emp;
	}

	@Override
	public List<Emp> selectAll() {
		List<Emp> emps = new ArrayList<>();
		try {
			Connection c = DBUtils.getConnection();
			String sql = "select * from emp_copy";
			PreparedStatement ps = c.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Emp emp = new Emp();
				emp.setEmpno(rs.getInt(1));
				emp.setEname(rs.getString(2));
				emp.setJob(rs.getString(3));
				emp.setMgr(rs.getInt(4));
				emp.setHiredate(rs.getDate(5));
				emp.setSal(rs.getInt(6));
				emp.setComm(rs.getInt(7));
				emp.setDeptno(rs.getInt(8));
				// 添加集合中
				emps.add(emp);
			}
			c.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return emps.isEmpty() ? Collections.emptyList() : emps;
	}

}
